package crm.control;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import crm.model.Recepit;
import java.util.List;


import crm.util.DateTime;
import crm.util.Pagination;
import java.sql.ResultSet;



import crm.dbcoon.DBConnect;
import crm.model.Recepit;
import crm.model.Statistics;
public class RecepitControl {
	
	/*
	 * 跟单相关方法
	 * @author Cyoung
	 * @date 2012 07 20
	 * 说明：跟单人所存信息格式（@跟单人Id@开始跟单阶段@结束阶段@回款数目@）
	 */
	public static Pagination pageInstance = null;
	
	public static Recepit getRecepitByOfferId(int offerId) {
		DBConnect dbc=new DBConnect();
		Recepit re=new Recepit();
		String sql="select control_recepit.* from control_offer,control_recepit where control_offer.recepit_id = control_recepit.id and control_offer.id='"+offerId+"'";
		dbc.prepareStatement(sql);
		ResultSet rs=null;
		try {
			rs=(ResultSet)dbc.executeQuery();
			if(rs.next()){
			getRs(rs, re);
			}
			return re;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRecepitByOfferId出错");
			return null;
		}finally{dbc.close();}
	}
	
	public static int getRecepitIdByTitle(String recepitTitle) {
		DBConnect dbc = new DBConnect();
		String sql="select id from control_recepit where title ='"+recepitTitle+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next()){
			Recepit recepit = new Recepit();
			recepit.setId(rs.getInt("id"));
			int id = recepit.getId();
			return id;}
			return 0;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}finally{dbc.close();}
	}
	
	
	public static Boolean recepitNameIsExis(String title) {
		DBConnect dbc=new DBConnect();
		String sql="select * from control_recepit where title='"+title+"'";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs=dbc.executeQuery();
			while (rs.next()) {
				System.out.println("不可以");
				return false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
		System.out.println("可以");
		return true;
	}
	
	public static boolean addRecepit(Recepit re) {
		DBConnect dbc = new DBConnect();
		String sql ="insert into control_recepit(consumer_id,creator,creat_time,intention,sort,stage,current_person,person1,person2,person3," +
				"predict_amount,predict_time,product,linkman_id,title) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
		dbc.prepareStatement(sql);
		try{
			setRs(dbc, re);
			dbc.executeUpdate();
			return true;
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("addRecepit方法出错");
			return false;
		}finally{
			dbc.close();
		}
	}
	
	public static boolean deleteRecepitById(int id) {
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from control_recepit where id="+id;
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("deleteRecepit出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public static boolean updateRecepit(Recepit re) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update control_recepit set consumer_id=?,creator=?,creat_time=?,intention=?,sort=?,stage=?,current_person=?,person1=?," +
				"person2=?,person3=?,predict_amount=?,predict_time=?,product=?,linkman_id=?,title=? where id = '"+re.getId()+"'";
		dbConnect.prepareStatement(sql);
		try {
			setRs(dbConnect, re);
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("updateRecepit出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public static Recepit getRecepitById(int id){
		DBConnect dbConnect = new DBConnect();
		Recepit re = new Recepit();
		String sql = "select * from control_recepit where id = '"+id+"'";
		dbConnect.prepareStatement(sql);
		ResultSet rSet = null;
		try {
			 rSet =(ResultSet) dbConnect.executeQuery();
			 if(rSet.next()){
				 getRs(rSet, re);
			 }
			 return re;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("findByid出错");
			return null;
		}finally{
			dbConnect.close();
		}
	}
	public static List<Recepit> getReceiptByStage(int stage){
		DBConnect dbc = new DBConnect();
		List<Recepit> list = new ArrayList<Recepit>();
		String sql = "select * from control_recepit where stage = '" + stage +"'";
		ResultSet rs = null;
		try{
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while(rs.next()){
				Recepit rec = new Recepit();
				getRs(rs,rec);
				list.add(rec);
			}
		} catch(SQLException e){
			e.printStackTrace();
		} finally{
			dbc.close();
			dbc.close(rs);
		}
		return list;
	}
	/**
	 * 根据id获取存当前跟单人的字段
	 * @param id
	 * @return
	 */
	public static String getCurrentPerson(int id){
		int flag=getTransferRecordNumber(id);
		Recepit recepit = getRecepitById(id);
		String person = "";
		if(flag==2)
			person = recepit.getPerson1();
		if(flag==3)
			person = recepit.getPerson2();
		if(flag==4)
			person = recepit.getPerson3();
		return person;
	}

	
	public static void setRs(DBConnect dbc,Recepit re) {
		//调用此方法需注意字段顺序
		int index=1;
		try{
		dbc.setInt(index++, re.getConsumer_id());
		dbc.setString(index++, re.getCreator());
		dbc.setString(index++, re.getCreat_time());
		dbc.setString(index++, re.getIntention());
		dbc.setString(index++, re.getSort());
		dbc.setInt(index++, re.getStage());
		dbc.setInt(index++, re.getCurrent_person());
		dbc.setString(index++, re.getPerson1());
		dbc.setString(index++, re.getPerson2());
		dbc.setString(index++,re.getPerson3());
		dbc.setString(index++, re.getPredict_amount());
		dbc.setString(index++, re.getPredict_time());
		dbc.setString(index++, re.getPrduct());
		dbc.setInt(index++, re.getLinkman_id());
		dbc.setString(index++, re.getTitle());
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("setRs出错");
	}
	}
	
	
	public static void getRs(ResultSet rs,Recepit re) {
		try {
			re.setConsumer_id(rs.getInt("consumer_id"));
			re.setCreat_time(rs.getString("creat_time"));
			re.setCreator(rs.getString("creator"));
			re.setId(rs.getInt("id"));
			re.setIntention(rs.getString("intention"));
			re.setCurrent_person(rs.getInt("current_person"));
			re.setPerson1(rs.getString("person1"));
			re.setPerson2(rs.getString("person2"));
			re.setPerson3(rs.getString("person3"));
			re.setPrduct(rs.getString("product"));
			re.setPredict_amount(rs.getString("predict_amount"));
			re.setPredict_time(rs.getString("predict_time"));
			re.setStage(rs.getInt("stage"));
			re.setSort(rs.getString("sort"));
			re.setTitle(rs.getString("title"));
			re.setLinkman_id(rs.getInt("linkman_id"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRs方法出错");
		}
		
	}
	
	/**
	 * 修改跟单状态
	 * 先修改control_recepit中
	 * @return
	 */
	public static boolean updateStage(int id,int stage) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update control_recepit set stage='"+stage+"' where id ='"+id+"'";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("更改recepit状态出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	/********************************************************************************
	 * 以下是处理 跟单移交问题的方法
	 * 读取数据库存移交人信息根据@拆分
	 * String[0]表示跟单人Id，string[1]表示跟单开始阶段，string[2]表示跟单结束阶段，string[3]表示跟单结束阶段时已达百分比（只有回款阶段需记录此字段）
	 *******************************************************************************/
	
	/**
	 * 跟单移交问题
	 * 需记录移交阶段，若是回款阶段要考虑回款多少
	 * @param id跟单号
	 * @param personId接收人号
	 * @param money前一个跟单人回款数
	 * @return
	 * 更新person以及currentPerson字段内容，先判断第几次移交
	 */
	public static boolean transferRecepit(int id,int newPersonId,String money){
		
		Recepit recepit = RecepitControl.getRecepitById(id);
		int flag = getTransferRecordNumber(id);
		String rootPerson = "";   //移交人
		String newPerson = "";    //接手人
		if(flag==1||flag==4)
			return false;
		if(flag==2)
			rootPerson = recepit.getPerson1();
		if(flag==3)
			rootPerson = recepit.getPerson2();
		String root[] = rootPerson.split("@");
		rootPerson = "@"+root[1]+"@"+root[2]+"@"+recepit.getStage()+"@"+money+"@";
		newPerson = "@"+newPersonId+"@"+recepit.getStage()+"@0@0@";  //接手人
		DBConnect dbConnect = new DBConnect();
		String sql = "update control_recepit set current_person = '"+newPersonId+"',person"+(flag-1)+"= '"+rootPerson+"',person"+flag+" = '"+newPerson+"'" +
			 		"where id ='"+id+"'"; 
		dbConnect.prepareStatement(sql);
		System.out.println(sql);
	     try {
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	
	/**
	 * 根据移交记录，判断移交时属于第几次（1,2,3）
	 * @return
	 */
	public static int getTransferRecordNumber(int id){
		Recepit re = new Recepit();
		re = getRecepitById(id);
		int flag = 0;
		if(re.getPerson1()==null||re.getPerson1().equals(""))
			flag =  1;
		else if(re.getPerson2()==null||re.getPerson2().equals(""))
			flag =  2;
		else if (re.getPerson3()==null||re.getPerson3().equals("")) 
			flag =  3;
		else 
			flag = 4;
		return flag;
 	}
	
	/**
	 * 更新回款(添加回款时执行此操作)
	 */
	public static boolean updateReturnedMoney (int recepitId,String add_money) {
		int personFlag = RecepitControl.getTransferRecordNumber(recepitId);
		String person = RecepitControl.getCurrentPerson(recepitId);
		String p[] = person.split("@");
		float hasReturned = Float.parseFloat(p[4]);
		float total = hasReturned+Float.parseFloat(add_money);
		String newPerson = "@"+p[1]+"@"+p[2]+"@"+p[3]+"@"+String.valueOf(total)+"@";
		DBConnect dbConnect=  new DBConnect();
		
		//在添加回款的时候，在statistics表中存一条记录
		Statistics s = new Statistics();
		s.setAdd_money(add_money);
		s.setDate(DateTime.Date().replace("-", "").trim());
		s.setEmployee_id(RecepitControl.getRecepitById(recepitId).getCurrent_person());
		s.setRecepit_id(recepitId);
		
		String sql = "update control_recepit set person"+(personFlag-1)+"= '"+newPerson+"' where id ='"+recepitId+"'";
		dbConnect.prepareStatement(sql);
		try {
			if(dbConnect.executeUpdate()&&StatisticsControl.addStatistics(s)){
			return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	/**
	 * 获取回款金额总额以及各销售员（3个，不管移交几次）回款数目
	 */
	public static float[]  getHuikuan(int recepitId){
		Recepit recepit =getRecepitById(recepitId);
		float huikuan[] = new float[3];
		huikuan[0]=0;huikuan[1]=0;huikuan[2]=0;
		String p[]= null;
		if(recepit.getPerson1()!=null&&!recepit.getPerson1().equals("")){
			p = recepit.getPerson1().split("@");
			huikuan[0] = Float.parseFloat(p[4]);
		}
		if(recepit.getPerson2()!=null&&!recepit.getPerson2().equals("")){
			p = recepit.getPerson2().split("@");
			huikuan[1] = Float.parseFloat(p[4]);
		}
		if(recepit.getPerson3()!=null&&!recepit.getPerson3().equals("")){
			p = recepit.getPerson3().split("@");
			huikuan[2] = Float.parseFloat(p[4]);
		}
		return huikuan;
	}
	/**
	 * 获取跟单回款总额
	 * @param recepitId
	 * @return
	 */
	public static float getTotalHuiKuan(int recepitId) {
		float huikuan[] =getHuikuan(recepitId);
		float total = 0;
		for(float h:huikuan){
			total+=h;
		}
		return total;
	}
	/**
	 * 在接手跟单之前已经回款的金额总数（不包括自己跟单期间的金额）
	 */
	public static float getTotalHuiKuanBefore(int recepitId ) {
		int flag = getTransferRecordNumber(recepitId);
		float totalMoneyBefore = 0;
		float huikuan[] = getHuikuan(recepitId);
		if(flag!=3){
		    totalMoneyBefore = 0;
		}
		if(flag==3){
			totalMoneyBefore= totalMoneyBefore+huikuan[0];
		}
		return totalMoneyBefore;
	}
	
	/**
	 * 获取当前跟单人在某次跟单期间回款数目
	 */
	public static float getMyHuiKuan(int recepitId) {
		return getTotalHuiKuan(recepitId)-getTotalHuiKuanBefore(recepitId);
	}
	
	
	/**
	 * 获取所有的跟单
	 */
	public static Collection<Recepit> getAllRecepits(){
		Collection<Recepit> recepits = new ArrayList<Recepit>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from control_recepit order by id";
		dbConnect.prepareStatement(sql);
		ResultSet rs = null;
		try {
			rs = dbConnect.executeQuery();
			while(rs.next()){
				Recepit recepit = new Recepit();
				getRs(rs, recepit);
				recepits.add(recepit);
			}
			return recepits;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}finally{
			dbConnect.close();
		}
	}
	
		/************************************************************************
		 ***********华丽的分割线*************************
		 ************************************************************************/
			    /***
			     * 获取跟单列表（管理员能看到所有的，普通销售员能看到自己参与过的跟单以及自己创建的（自己不一定跟过））
			     * 包括存在跟单移交的
			     */
			public  static Collection getAllRecepits(String title,String creator,String consumerName,String sort, String creatTime,String currentPerson,
				String stage, String uid,int pages,int rowPerPage){
			//	System.out.println(creator+" "+consumerName+""+sort+""+creatTime+""+currentPerson+"DDD"+uid);
				String myname="%";
				if(title==null||title.equals(""))
					title="%";
				if(consumerName==null||consumerName.equals(""))
					consumerName="%";
				if(creator==null||creator.equals(""))
					creator="%";
				if(creatTime==null||creatTime.equals(""))
					creatTime="%";
				if(stage==null||stage.equals(""))
					stage="%";
				if(sort==null||sort.equals(""))
					sort="%";
				if(currentPerson==null||currentPerson.equals(""))
					currentPerson="%";
				if(uid!=null&&!uid.equals("")){
					myname = EmployeesControl.getEmpolyeesById(Integer.parseInt(uid)).getName();
					uid="@"+uid+"@%";
					
				}
				if(uid==null||uid.equals("")){
					uid="%";
				}
				
				String sql="select control_recepit.* from control_recepit,info_employees,info_customer where  control_recepit.title like '%"+title+"%' and " +
						"control_recepit.creator like '%"+creator+"%' and control_recepit.consumer_id = info_customer.id and info_customer.name like '%"+consumerName+"%' " +
								"and  control_recepit.sort like '"+sort+"' and control_recepit.creat_time like '%"+creatTime+"%' and info_employees.id = control_recepit.current_person " +
										" and info_employees.name like '%"+currentPerson+"%' and control_recepit.stage like'"+stage+"' and (control_recepit.person1 like '"+uid+"' or " +
												" control_recepit.person2 like '"+uid+"' or control_recepit.person3 like '"+uid+"' or control_recepit.creator like '%"+myname+"%' ) order by" +
														" control_recepit.id desc";
				System.out.println(sql);
				pageInstance = new RecepitControlPage(sql, rowPerPage);
				try {
					return pageInstance.getPage(pages);
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
					return null;
				}
			}

}
class RecepitControlPage extends Pagination {
	
	public RecepitControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	
	protected Collection<Recepit> packResultSet(ResultSet rs) {
		Collection<Recepit> recepits = new ArrayList<Recepit>();
		try{
			while(rs.next()){
				Recepit re =new Recepit();
				RecepitControl.getRs(rs, re);
				recepits.add(re);
				re = null;
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<recepit>出错");
		}
		return recepits;
	}

}

